package com.situ.staff.department.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
//import java.util.HashMap;
import java.util.List;
//import java.util.Map;

import com.situ.staff.department.model.DepartmentModel;
import com.situ.staff.util.FmtEmpty;
import com.situ.staff.util.JDBCUtil;

public class DepartmentDao {
	/**
	 * 添加
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int insert(DepartmentModel model) throws SQLException {
		String sql = "insert into department (code,name,tel) values(?,?,?)";
		return JDBCUtil.update(sql, model.getCode(), model.getName(), model.getTel());
	}

	/**
	 * 批量添加
	 * 
	 * @param list
	 * @return
	 * @throws SQLException
	 */
//	public List<Integer> insert(List<DepartmentModel> list) throws SQLException {
//		List<Integer> res = new ArrayList<Integer>();
//		for (DepartmentModel model : list) {
//			insert(model);
//		}
//		return res;
//	}

	/**
	 * 通过号码删除
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int deleteByCode(DepartmentModel model) throws SQLException {
		String sql = "delete from department where code =?";
		return JDBCUtil.update(sql.toString(), model.getCode());
	}

	/**
	 * 删除
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int delete(DepartmentModel model) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from department");
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 更新所有字段
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int updateAll(DepartmentModel model) throws SQLException {
		String sql = "update department set name = ? , tel = ? where code =?";
		return JDBCUtil.update(sql, model.getName(), model.getTel(), model.getCode());
	}

	/**
	 * 更新部分字段
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int updateActive(DepartmentModel model) throws SQLException {
//		String sql = "update department set name = ? and tel = ? where code =?";
		StringBuffer sql = new StringBuffer("update department set id=id ");
		List<Object> vals = new ArrayList<Object>();
		String name = model.getName();
		if (name != null) {
			sql.append(" ,name=?");
			vals.add(name);
		}
		String tel = model.getTel();
		if (tel != null) {
			sql.append(" ,tel=?");
			vals.add(tel);
		}
		sql.append(" where code = ?");
		vals.add(model.getCode());
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 查询所有
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<DepartmentModel> selectList(DepartmentModel model) throws SQLException {
		StringBuffer sql = new StringBuffer(
				"select code,name,tel,(select count(1) from employee where code_dept = department.code) count from department ");
		List<Object> vals = appendWhere(sql, model);
		List<String> props = Arrays.asList("code", "name", "tel","count");
		return JDBCUtil.query(sql.toString(), vals, DepartmentModel.class, props);
	}

	/**
	 * 条件拼接和sql拼接
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendWhere(StringBuffer sql, DepartmentModel model) {
		// TODO Auto-generated method stub
		List<Object> vals = new ArrayList<Object>();
		sql.append(" where 1=1 ");
		String code = model.getCode();
		if (!FmtEmpty.isEmpty(code)) {
			vals.add(code);
			sql.append(" and code like ?");
		}
		String name = model.getName();
		if (!FmtEmpty.isEmpty(name)) {
			vals.add(name);
			sql.append(" and name like ?");
		}
		String tel = model.getTel();
		if (!FmtEmpty.isEmpty(tel)) {
			vals.add(tel);
			sql.append(" and tel like ?");
		}
		if(model.isPageOn()) {
			sql.append(" limit ?,?");
			vals.add(model.getRowStart());
			vals.add(model.getPageLimit());
		}
		return vals;
	}

	/**
	 * 查询个体
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public DepartmentModel selectModel(DepartmentModel model) throws SQLException {
		StringBuffer sql = new StringBuffer("select code,name,tel from department");
		List<Object> vals = appendWhere(sql, model);
		List<String> props = Arrays.asList("code", "name", "tel");
		return JDBCUtil.queryModel(sql.toString(), vals, DepartmentModel.class, props);
	}

	public Integer selectCount(DepartmentModel m0) {
		StringBuffer sql = new StringBuffer("select count(1) from department ");
		List<Object> vals = appendWhere(sql, m0);
		return JDBCUtil.queryInt(sql.toString(), vals);
	}

}
